import pymysql
import configparser
import os
from pymysql.err import OperationalError, ProgrammingError, IntegrityError

class DatabaseError(Exception):
    """自定义数据库异常类"""
    pass

class ThinkDB:
    def __init__(self, config_file='config.ini'):
        """
        初始化数据库连接配置
        :param config_file: 配置文件路径，默认为项目根目录下的 'config.ini'
        """
        self.config_file = config_file
        self.config = self._load_config()
        self.connection = None
        self.cursor = None

        # SQL 构建器状态
        self._table = None
        self._where = []
        self._where_params = []
        self._order = []
        self._limit = None
        self._field = '*'

    def _find_project_root(self, start_path):
        """
        从起始路径开始向上查找项目根目录。
        查找逻辑：寻找包含 'config.ini' 文件的目录。
        """
        current_path = os.path.abspath(start_path)
        while True:
            config_path = os.path.join(current_path, self.config_file)
            if os.path.exists(config_path):
                return current_path
            parent_path = os.path.dirname(current_path)
            if parent_path == current_path:
                raise FileNotFoundError(
                    f"在所有上级目录中均未找到配置文件 '{self.config_file}'。请确保它位于项目根目录下。")
            current_path = parent_path

    def _load_config(self):
        """从项目根目录加载数据库配置"""
        current_file_dir = os.path.dirname(os.path.abspath(__file__))
        project_root = self._find_project_root(current_file_dir)
        config_file_path = os.path.join(project_root, self.config_file)

        if not os.path.exists(config_file_path):
            raise FileNotFoundError(f"数据库配置文件 '{config_file_path}' 不存在。")

        config = configparser.ConfigParser()
        try:
            config.read(config_file_path, encoding='utf-8')
        except UnicodeDecodeError:
            raise DatabaseError(f"配置文件 '{config_file_path}' 编码错误，请使用 UTF-8 编码。")

        if 'database' not in config.sections():
            raise DatabaseError(f"配置文件 '{config_file_path}' 中缺少 'database' 节。")

        db_config = {}
        try:
            db_config['host'] = config.get('database', 'host')
            db_config['port'] = config.getint('database', 'port')
            db_config['user'] = config.get('database', 'user')
            db_config['password'] = config.get('database', 'password')
            db_config['database'] = config.get('database', 'database')
            db_config['charset'] = config.get('database', 'charset', fallback='utf8mb4')
            db_config['cursorclass'] = pymysql.cursors.DictCursor
        except configparser.NoOptionError as e:
            raise DatabaseError(f"配置文件错误: {e}")

        return db_config

    def _connect(self):
        """建立数据库连接"""
        if not self.connection or not self.connection.open:
            try:
                self.connection = pymysql.connect(**self.config)
                self.cursor = self.connection.cursor()
            except OperationalError as e:
                raise DatabaseError(f"数据库连接失败: {e}")

    def _close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.open:
            self.connection.close()

    def _reset(self):
        """重置查询构建器状态，以便进行下一次查询"""
        self._table = None
        self._where = []
        self._where_params = []
        self._order = []
        self._limit = None
        self._field = '*'

    def table(self, table_name):
        """指定要操作的数据表"""
        self._table = table_name
        return self

    def field(self, fields):
        """指定要查询的字段"""
        if isinstance(fields, list):
            self._field = ', '.join(fields)
        else:
            self._field = fields
        return self

    def where(self, field, operator, value=None):
        """
        添加查询条件 (推荐用法：字段, 运算符, 值)

        支持:
        1. 标准用法 (推荐):
           where('id', '=', 1)
           where('age', '>', 18)
           where('name', 'LIKE', '%张三%')
           where('id', 'IN', [1, 2, 3])

        2. 复杂条件 (直接传入SQL片段和参数):
           where('created_at BETWEEN %s AND %s', ['2023-01-01', '2023-12-31'])
           (注意：这种用法下，operator参数会被当作SQL片段，value参数当作参数列表)
        """
        # 复杂条件用法判断: 如果只传了两个参数，或者第三个参数是列表/元组且operator看起来像SQL片段
        if value is None or (isinstance(value, (list, tuple)) and '%s' in str(operator)):
            # 用法: where('sql %s', [params])
            condition = field
            params = operator if value is None else value
        else:
            # 标准用法: where('field', 'op', value)
            escaped_field = f"`{field}`"
            if operator.upper() in ('IN', 'NOT IN'):
                if not isinstance(value, (list, tuple)):
                    raise DatabaseError(f"使用 '{operator}' 运算符时，值必须是列表或元组")
                placeholders = ', '.join(['%s'] * len(value))
                condition = f"{escaped_field} {operator.upper()} ({placeholders})"
                params = value
            else:
                condition = f"{escaped_field} {operator} %s"
                params = [value]

        self._where.append(condition)
        if params:
            if isinstance(params, (list, tuple)):
                self._where_params.extend(params)
            else:
                self._where_params.append(params)

        return self

    def order(self, field, direction='ASC'):
        """添加排序条件"""
        self._order.append(f"{field} {direction}")
        return self

    def limit(self, offset, length=None):
        """添加 LIMIT 限制"""
        if length is None:
            self._limit = f"{offset}"
        else:
            self._limit = f"{offset}, {length}"
        return self

    def _build_sql(self, sql_type='SELECT'):
        """构建最终的 SQL 语句"""
        if not self._table:
            raise DatabaseError("必须通过 table() 方法指定数据表")

        sql_parts = []
        if sql_type == 'SELECT':
            sql_parts.append(f"SELECT {self._field} FROM `{self._table}`")
        elif sql_type == 'DELETE':
            sql_parts.append(f"DELETE FROM `{self._table}`")

        if self._where:
            sql_parts.append(f"WHERE {' AND '.join(self._where)}")
        if self._order:
            sql_parts.append(f"ORDER BY {', '.join(self._order)}")
        if self._limit:
            sql_parts.append(f"LIMIT {self._limit}")

        return ' '.join(sql_parts)

    def find(self):
        """查询单条记录"""
        self.limit(1)
        result = self.select()
        return result[0] if result else None

    def select(self):
        """查询多条记录"""
        try:
            self._connect()
            sql = self._build_sql('SELECT')
            self.cursor.execute(sql, tuple(self._where_params))
            results = self.cursor.fetchall()
            return results
        except ProgrammingError as e:
            raise DatabaseError(f"SQL查询错误: {e}")
        finally:
            self._reset()
            self._close()

    def insert(self, data):
        """插入单条记录"""
        if not self._table: raise DatabaseError("必须通过 table() 方法指定数据表")
        if not isinstance(data, dict): raise DatabaseError("插入的数据必须是字典类型")

        fields = ', '.join([f'`{k}`' for k in data.keys()])
        placeholders = ', '.join(['%s'] * len(data))
        values = list(data.values())
        sql = f"INSERT INTO `{self._table}` ({fields}) VALUES ({placeholders})"

        try:
            self._connect()
            self.cursor.execute(sql, tuple(values))
            self.connection.commit()
            return self.cursor.lastrowid
        except IntegrityError as e:
            self.connection.rollback()
            raise DatabaseError(f"插入数据失败 (完整性约束): {e}")
        except Exception as e:
            self.connection.rollback()
            raise DatabaseError(f"插入数据失败: {e}")
        finally:
            self._reset()
            self._close()

    def update(self, data):
        """更新记录"""
        if not self._table: raise DatabaseError("必须通过 table() 方法指定数据表")
        if not isinstance(data, dict): raise DatabaseError("更新的数据必须是字典类型")
        if not self._where: raise DatabaseError("更新操作必须包含 where 条件")

        set_clause = ', '.join([f'`{k}` = %s' for k in data.keys()])
        values = list(data.values()) + self._where_params
        sql = f"UPDATE `{self._table}` SET {set_clause} WHERE {' AND '.join(self._where)}"

        try:
            self._connect()
            affected_rows = self.cursor.execute(sql, tuple(values))
            self.connection.commit()
            return affected_rows
        except Exception as e:
            self.connection.rollback()
            raise DatabaseError(f"更新数据失败: {e}")
        finally:
            self._reset()
            self._close()

    def delete(self):
        """删除记录"""
        if not self._table: raise DatabaseError("必须通过 table() 方法指定数据表")
        if not self._where: raise DatabaseError("删除操作必须包含 where 条件")

        try:
            self._connect()
            sql = self._build_sql('DELETE')
            affected_rows = self.cursor.execute(sql, tuple(self._where_params))
            self.connection.commit()
            return affected_rows
        except Exception as e:
            self.connection.rollback()
            raise DatabaseError(f"删除数据失败: {e}")
        finally:
            self._reset()
            self._close()